/*******************************************************************************
 * Copyright (c) 2005, 2014 springside.github.io
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 *******************************************************************************/
package net.wofly.sale.stock.repository;

import java.util.List;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.PagingAndSortingRepository;
import org.springframework.data.repository.query.Param;

import net.wofly.sale.report.domain.StockDTO;
import net.wofly.sale.stock.domain.Stock;

public interface StockRepository
        extends PagingAndSortingRepository<Stock, String>, JpaSpecificationExecutor<Stock> {
    
    Stock findByWarehouseIDAndCommodityTerminalID(String warehouseID, String commodityTerminalID);
    
    List<Stock> findByWarehouseIDAndNumGreaterThan(String warehouseID, Integer num);

    List<Stock> findByWarehouseID(String warehouseID);

    /**
     * 查询库存数量
     *
     * @return
     */
    @Query(value = "select new StockDTO(u.userID, u.name, s.commodityTerminalID, ct.commodityTerminalName, sum(s.num)) " +
            "from Stock s, User u, CommodityTerminal ct " +
            "where s.warehouseID = u.warehouseID and s.commodityTerminalID = ct.commodityTerminalID " +
            "group by u.userID, u.name, s.commodityTerminalID, ct.commodityTerminalName")
    List<StockDTO> findByCountDate();
    
    @Query(value = "from Stock b , CommodityTerminal c where b.commodityTerminalID=c.commodityTerminalID and b.warehouseID =:warehouseID order by c.commodityTerminalCode asc")
    Page<Object[]> findByWarehouseID(@Param("warehouseID") String warehouseID , Pageable pageable) ;
    
}